package com.yuzhenxin.coursesystem.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yuzhenxin.coursesystem.entity.SelectionDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface SelectionMapper extends BaseMapper<SelectionDO> {

    @Select("select teacher_name,course_name,credit,count(course_name) total from selection " +
            "inner join course on selection.course_number=course.number " +
            "group by course_number " +
            "order by total desc limit ${current},${limit}")
    List<Map<String, Object>> selectStudentCount(int current, int limit);

    @Select("select selection.student_number,student.`name`,count(selection.course_number) count\n" +
            "from selection\n" +
            "INNER JOIN student on selection.student_number=student.number")
    List<Map<String, Object>> selectCourseCount();

    @Select("select selection.student_number,student.`name` student_name,selection.course_number,course.`name` course_name,course.credit\n" +
            "from selection\n" +
            "INNER JOIN student on selection.student_number=student.number\n" +
            "INNER JOIN course on selection.course_number=course.number\n" +
            "where selection.student_number='${number}'")
    List<Map<String, Object>> selectByStudentId(Integer number);

    @Select("select selection.student_number,student.`name` student_name,selection.course_number,course.`name` course_name,course.credit\n" +
            "from selection\n" +
            "INNER JOIN student on selection.student_number=student.number\n" +
            "INNER JOIN course on selection.course_number=course.number")
    List<Map<String, Object>> selectList();

    @Select("select teacher_name, course_name, student_name, score\n" +
            "from selection\n" +
            "inner join teacher\n" +
            "inner join course\n" +
            "inner join student\n" +
            "where selection.course_id = course.course_id and selection.student_id = student.student_id\n" +
            "and teacher_id = '${teacherId}' and course_name like '%${courseName}%'\n" +
            "limit ${currentPage},${pageSize}")
    List<Map<String, Object>> selectByPageAndTeacherId(@Param("teacherId") Integer teacherId,
                                                       @Param("courseName") String courseName,
                                                       @Param("currentPage") int currentPage,
                                                       @Param("pageSize") int pageSize);

}
